//HI
// ugh
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package bridge;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.pool.impl.GenericObjectPool;

/**
 * This is a singleton class which provides some wrapper functions
 * around the Database
 * @author Matthew Poulos
 */
public class DatabaseAccessor {
	private static final DatabaseAccessor INSTANCE = new DatabaseAccessor();
    
	
	/**
     * @return An instance of the DatabaseAccessor
     */
    public static DatabaseAccessor getInstance() {
        return INSTANCE;
    }

    //public static Connection conn = null;
    private DataSource dataSource;
	private GenericObjectPool connectionPool;


    private DatabaseAccessor() {
       this("jdbc:mysql://localhost/sensor_data", "root", "");
    }

    private DatabaseAccessor(String url, String username, String password) {
    	try {
            Class.forName ("com.mysql.jdbc.Driver");

            connectionPool = new GenericObjectPool(null);
            ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(url, username, password);
            PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory,connectionPool,null,null,false,true);
            dataSource = new PoolingDataSource(connectionPool);
        } catch(ClassNotFoundException e) {
            e.printStackTrace();
        } catch(Exception e) {
            e.printStackTrace();
        }
    }

    public String test() { return "WURKS20!!<br/><br/>"; }
    
    /**
     * This method returns an array of pairs which contain
     * a time stamp and a value in ascending order according to time
     * @param sensorID the ID of the sensor to query
     * @param timeInitial The initial time
     * @param timeFinal The final time
     * @return An array of data points as pairs
     */
    public ArrayList<Pair<Long,Float>> getRawData(byte sensorID, long timeInitial, long timeFinal) throws SQLException {
    	Connection conn = null;
    	try {
        	conn = dataSource.getConnection();
        	
        	String query = 	"SELECT time, value FROM Data "
        				+	"WHERE sensorID=? AND time BETWEEN ? AND ?";
			System.out.println(query);
        	PreparedStatement stmt = conn.prepareStatement(query);
        	stmt.setByte(1, sensorID);
        	stmt.setLong(2, timeInitial);
        	stmt.setLong(3, timeFinal);
        	
            ResultSet set = stmt.executeQuery();
            ArrayList<Pair<Long,Float>> list = new ArrayList<Pair<Long,Float>>();

            Pair<Long,Float> pair;
            while(set.next()) {
                pair = new Pair<Long,Float>(set.getLong("time"), set.getFloat("value"));
                list.add(pair);
            }

            set.close();
            stmt.close();
            conn.close();
            
			return list;
			
        } catch(SQLException e) {
        	if (conn != null) try { conn.close(); } catch (SQLException sqle) {}
            e.printStackTrace();
            throw e;
        }
    }
    
    /**
     * This method returns an array of pairs which contain
     * a time stamp that is within a certain number of milliseconds
     * of the last reading recorded
     * @param sensorID the ID of the sensor to query
     * @param tailTime the number of milliseconds of readings to return, taken from the most recent readings
     * @return An array of data points as pairs
     */
	  
    public ArrayList<Pair<Long,Float>> getTailData(byte sensorID, long window) throws SQLException {
        Connection conn = null;
    	try {
        	conn = dataSource.getConnection();
        	
			long currentTime = System.currentTimeMillis();
			long tailTime = System.currentTimeMillis() - window;
			
        	String query = 	"SELECT D.time, D.value FROM Data D "
        				+	"WHERE D.sensorID=? AND D.time >= "
        //				+		"(SELECT MAX(D2.time) FROM Data D2 "
        	//			+		"WHERE D2.sensorID = ?) - ?";
						+	"? ORDER BY D.time";
			
        	System.out.println(query);
        	PreparedStatement stmt = conn.prepareStatement(query);
        	stmt.setByte(1, sensorID);
		//	stmt.setByte(2, sensorID);
        //	stmt.setLong(3, tailTime);
        	stmt.setLong(2, tailTime);
			
            ResultSet set = stmt.executeQuery();
            ArrayList<Pair<Long,Float>> list = new ArrayList<Pair<Long,Float>>();

            Pair<Long,Float> pair;
            while(set.next()) {
                pair = new Pair<Long,Float>(set.getLong("time"), set.getFloat("value"));
                list.add(pair);
            }

            set.close();
            stmt.close();
            conn.close();
            
			return list;
        } catch(SQLException e) {
        	if (conn != null) try { conn.close(); } catch (SQLException sqle) {}
            e.printStackTrace();
            throw e;
        }
    }

	public SensorType[] getSensorTypes() throws SQLException
	{
		ArrayList<SensorType> types = new ArrayList<SensorType>(7);
    	
    	Connection conn = null;
        try 
		{
        	conn = dataSource.getConnection();
        	
        	String query = 	"SELECT type, min, max, unit, refresh, window FROM SensorTypes";;

        	Statement stmt = conn.createStatement();
        	ResultSet set = stmt.executeQuery(query);

            while(set.next()) {
            	String type = set.getString(1);
            	float min = set.getFloat(2);
            	float max = set.getFloat(3);
				String unit = set.getString(4);
				int refresh = set.getInt(5);
				int window = set.getInt(6);
            	types.add(new SensorType(type, min, max, unit, refresh, window));
            }

            set.close();
            stmt.close();
            conn.close();

            return types.toArray(new SensorType[types.size()]);
        } catch(SQLException e) {
        	if (conn != null) try { conn.close(); } catch (SQLException sqle) {}
            e.printStackTrace();
            throw e;
        }
	
	}
	
	public String modifySensorType(String old_type, String new_type, String unit, String min, String max, String refresh, String window) throws SQLException
	{
		Connection conn = null;
		try 
		{
			conn = dataSource.getConnection();

			String query = 	"UPDATE SensorTypes SET type=?, unit=?, min=?, max=?, refresh=?, window=? WHERE type=?";
        	PreparedStatement pstmt = conn.prepareStatement(query);
			pstmt.setString(1, new_type);
			pstmt.setString(2, unit);
			pstmt.setFloat(3, Float.parseFloat(min));
			pstmt.setFloat(4, Float.parseFloat(max));
			pstmt.setInt(5, Integer.parseInt(refresh));
			pstmt.setInt(6, Integer.parseInt(window));
			pstmt.setString(7, old_type);

			pstmt.executeUpdate();
			String result = query;
        	
			// id change means we need to update descriptions and update data table
			if(new_type.equals(old_type) == false)
			{
				pstmt.close();
				query = "UPDATE Sensors SET type=? WHERE type=?";
				pstmt = conn.prepareStatement(query);
				pstmt.setString(1,new_type);
				pstmt.setString(2,old_type);
				pstmt.executeUpdate();
				result += "\n" + query;
			}
			
			pstmt.close();
            conn.close();
                      
			return result;
        } 
		catch(SQLException e) 
		{
        	if (conn != null) try { conn.close(); } catch (SQLException sqle) {}
            e.printStackTrace();
            throw e;
        }
	}
	
	public String addSensorType(String new_type, String unit, String min, String max, String refresh, String window) throws SQLException
	{
		Connection conn = null;
		try 
		{
			conn = dataSource.getConnection();
        	
			String query = 	"INSERT INTO SensorTypes (type, unit, min, max, refresh, window) VALUES (?, ?, ?, ?, ?, ?)";
        	PreparedStatement pstmt = conn.prepareStatement(query);
			pstmt.setString(1, new_type);
			pstmt.setString(2, unit);
			pstmt.setFloat(3, Float.parseFloat(min));
			pstmt.setFloat(4, Float.parseFloat(max));
			pstmt.setInt(5, Integer.parseInt(refresh));
			pstmt.setInt(6, Integer.parseInt(window));
			
			String result = "" + pstmt.executeUpdate();
			pstmt.close();
			result += "\n" + query;
			
			conn.close();
			return result;
        } 
		catch(SQLException e) 
		{
        	if (conn != null) try { conn.close(); } catch (SQLException sqle) {}
            e.printStackTrace();
            throw e;
        }
	}
	
	public String[] getTypes() throws SQLException {
		ArrayList<String> types = new ArrayList<String>(5);
		Connection conn = null;
        try 
		{
        	conn = dataSource.getConnection();
			Statement stmt = conn.createStatement();
			
			ResultSet set = stmt.executeQuery("SELECT type FROM SensorTypes ORDER BY type");
			while (set.next())
				types.add(set.getString("type"));
			
			set.close();
            stmt.close();
            conn.close();
			
			return types.toArray(new String[types.size()]);
		} 
		catch(SQLException e) 
		{
        	if (conn != null) try { conn.close(); } catch (SQLException sqle) {}
            e.printStackTrace();
            throw e;
        }
	}
	
    public String[] getSensorNames() throws SQLException {
    	ArrayList<String> names = new ArrayList<String>(8);
    	
    	Connection conn = null;
        try {
        	conn = dataSource.getConnection();
        	
        	String query = 	"SELECT S.name FROM Sensors S";

        	Statement stmt = conn.createStatement();
            ResultSet set = stmt.executeQuery(query);

            while(set.next()) {
            	names.add(set.getString(1));
            }

            set.close();
            stmt.close();
            conn.close();
            
            return names.toArray(new String[names.size()]);
        } catch(SQLException e) {
        	if (conn != null) try { conn.close(); } catch (SQLException sqle) {}
            e.printStackTrace();
            throw e;
        }
    }
	
	public String modifySensor(String old_id, String new_id, String name, String type, String description) throws SQLException
	{
		Connection conn = null;
		try 
		{
			conn = dataSource.getConnection();
        	

			String query = 	"UPDATE Sensors SET sensorID=?, name=?, type=? WHERE sensorID=?";
			//String query = 	"UPDATE Sensors SET sensorID=" + new_id + ", name='" + name + "', type='" + type + "' WHERE sensorID=" + old_id;
        	PreparedStatement pstmt = conn.prepareStatement(query);
			pstmt.setByte(1, Byte.parseByte(new_id));
			pstmt.setString(2, name);
			pstmt.setString(3, type);
			pstmt.setByte(4, Byte.parseByte(old_id));

			pstmt.executeUpdate();
			String result = query;
        	
		
			// id change means we need to update descriptions and update data table
			if(new_id.equals(old_id) == false)
			{
				pstmt.close();
				query = "UPDATE Data SET sensorID=? WHERE sensorID=?";
				//query = "UPDATE Data SET sensorID=" + new_id + " WHERE sensorID=" + old_id;
				pstmt = conn.prepareStatement(query);
				pstmt.setByte(1, Byte.parseByte(new_id));
				pstmt.setByte(2, Byte.parseByte(old_id));
				pstmt.executeUpdate();
				result += "\n" + query;
			}
			
			pstmt.close();
			
			query = "UPDATE SensorDescriptions SET sensorID=?, description=? WHERE sensorID=?";
			//query = "UPDATE SensorDescriptions SET sensorID=" + new_id + ", description='" + description + "' WHERE sensorID=" + old_id;
			pstmt = conn.prepareStatement(query);
			pstmt.setByte(1, Byte.parseByte(new_id));
			pstmt.setString(2, description);
			pstmt.setByte(3, Byte.parseByte(old_id));
			pstmt.executeUpdate();
			result += "\n" + query;

			pstmt.close();
            conn.close();
                      
			return result;
        } 
		catch(SQLException e) 
		{
        	if (conn != null) try { conn.close(); } catch (SQLException sqle) {}
            e.printStackTrace();
            throw e;
        }
	}
	
	public String addSensor(String new_id, String name, String type, String description) throws SQLException
	{
		Connection conn = null;
		try 
		{
			conn = dataSource.getConnection();
        	
			String query = 	"INSERT INTO Sensors (sensorID, name, type) VALUES (?, ?, ?)";
        	PreparedStatement pstmt = conn.prepareStatement(query);
			pstmt.setByte(1, Byte.parseByte(new_id));
			pstmt.setString(2, name);
			pstmt.setString(3, type);

			String result = "" + pstmt.executeUpdate();
			pstmt.close();
			result += "\n" + query;
			
			query = "INSERT INTO SensorDescriptions (sensorID, description) VALUES (?, ?)";
			pstmt = conn.prepareStatement(query);
			pstmt.setByte(1, Byte.parseByte(new_id));
			pstmt.setString(2, description);
			result += "\n" + pstmt.executeUpdate();
			pstmt.close();
			result += "\n" + query;
		
			conn.close();
			return result;
        } 
		catch(SQLException e) 
		{
        	if (conn != null) try { conn.close(); } catch (SQLException sqle) {}
            e.printStackTrace();
            throw e;
        }
	}
	
    public Sensor[] getSensors() throws SQLException 
	{
    	ArrayList<Sensor> sensors = new ArrayList<Sensor>(16);
    	
    	Connection conn = null;
        try {
        	conn = dataSource.getConnection();
        	
        	String query = 	"SELECT S.sensorID, S.type, S.name, ST.min, ST.max, ST.unit, ST.refresh, ST.window FROM Sensors S, SensorTypes ST "
        				+	"WHERE S.type = ST.type ORDER BY S.sensorID";

        	Statement stmt = conn.createStatement();
        	ResultSet set = stmt.executeQuery(query);

            while(set.next()) {
            	byte sensorID = set.getByte(1);
            	String type = set.getString(2);
            	String name = set.getString(3);
            	float min = set.getFloat(4);
            	float max = set.getFloat(5);
				String unit = set.getString(6);
				int refresh = set.getInt(7);
				int window = set.getInt(8);
            	sensors.add(new Sensor(sensorID, name, new SensorType(type, min, max, unit, refresh, window)));
            }

            set.close();
            stmt.close();
            conn.close();

            return sensors.toArray(new Sensor[sensors.size()]);
        } catch(SQLException e) {
        	if (conn != null) try { conn.close(); } catch (SQLException sqle) {}
            e.printStackTrace();
            throw e;
        }
    }
	
	public Sensor[] getSensorsForViz() throws SQLException 
	{
    	ArrayList<Sensor> sensors = new ArrayList<Sensor>(16);
    	
    	Connection conn = null;
        try {
        	conn = dataSource.getConnection();
        	
        	String query = 	"SELECT S.sensorID, S.type, S.name, ST.min, ST.max, ST.unit, ST.refresh, ST.window FROM Sensors S, SensorTypes ST "
        				+	"WHERE S.type = ST.type ORDER BY S.type";

        	Statement stmt = conn.createStatement();
        	ResultSet set = stmt.executeQuery(query);

            while(set.next()) {
            	byte sensorID = set.getByte(1);
            	String type = set.getString(2);
            	String name = set.getString(3);
            	float min = set.getFloat(4);
            	float max = set.getFloat(5);
				String unit = set.getString(6);
				int refresh = set.getInt(7);
				int window = set.getInt(8);
            	sensors.add(new Sensor(sensorID, name, new SensorType(type, min, max, unit, refresh, window)));
            }

            set.close();
            stmt.close();
            conn.close();

            return sensors.toArray(new Sensor[sensors.size()]);
        } catch(SQLException e) {
        	if (conn != null) try { conn.close(); } catch (SQLException sqle) {}
            e.printStackTrace();
            throw e;
        }
    }

	public Sensor getSensor(byte id) throws SQLException
	{
		Connection conn = null;
		try 
		{
			conn = dataSource.getConnection();
        	String query = 	"SELECT S.sensorID, S.type, S.name, ST.min, ST.max, ST.unit, ST.refresh, ST.window FROM Sensors S, SensorTypes ST "
        				+	"WHERE S.type = ST.type AND S.sensorID = " + (0xff & id);

        	Statement stmt = conn.createStatement();
        	ResultSet set = stmt.executeQuery(query);

        	Sensor sensor = null;
            if(set.next())
			{
            	byte sensorID = set.getByte(1);
            	String type = set.getString(2);
            	String name = set.getString(3);
            	float min = set.getFloat(4);
            	float max = set.getFloat(5);
				String unit = set.getString(6);
				int refresh = set.getInt(7);
				int window = set.getInt(8);
            	sensor = new Sensor(sensorID, name, new SensorType(type, min, max, unit, refresh, window)); 
            }
            set.close();
            stmt.close();
            conn.close();
			return sensor;
        } catch(SQLException e) {
        	if (conn != null) try { conn.close(); } catch (SQLException sqle) {}
            e.printStackTrace();
            throw e;
        }
		
	}
	
	public Map<Byte, String> getSensorDescriptions() throws SQLException {
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
			String query = "SELECT D.sensorID, D.description FROM SensorDescriptions D";

			Statement stmt = conn.createStatement();
			ResultSet set = stmt.executeQuery(query);

	    	Map<Byte, String> descs = new HashMap<Byte, String>(8);

	    	while (set.next())
	    		descs.put(set.getByte(1), set.getString(2));

	    	set.close();
			stmt.close();
			conn.close();

			return descs;
		} catch (SQLException e) {
			if (conn != null) try { conn.close(); } catch (SQLException sqle) {}
			e.printStackTrace();
			throw e;
		}
	}
	
	public String getSensorDescription(byte id) {
		Connection conn = null;
		try 
		{
			conn = dataSource.getConnection();
        	String query = 	"SELECT D.description FROM SensorDescriptions D WHERE D.sensorID = " + (0xff & id);

        	Statement stmt = conn.createStatement();
        	ResultSet set = stmt.executeQuery(query);

        	String result = null;
            if(set.next())
			{
            	result = set.getString(1);
            }
            set.close();
            stmt.close();
            conn.close();
			return result;
        } catch(SQLException e) {
        	if (conn != null) try { conn.close(); } catch (SQLException sqle) {}
            e.printStackTrace();
            return "Error: Description not found";
        }
		
	}
}
